﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using NLog;

namespace ExcelToTPI.Utility
{
    public static class Tools
    {
        /// <summary>
        /// 大数据量情况下导出Excel
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="worker"></param>
        /// <param name="e"></param>
        /// <param name="argument"></param>
        public static void LargeExport(IDataReader reader, BackgroundWorker worker, DoWorkEventArgs e, WorkerArgument argument)
        {
            try
            {
                int sheetCount = Convert.ToInt32(Math.Ceiling((double)argument.RecordCount / argument.ExcelCapacity));
                int iProcessedCount = 0;
                for (int sheetIndex = 1; sheetIndex <= sheetCount; sheetIndex++)
                {
                    string excelFileName = $@"{argument.ExcelSavePath}\{argument.DatabaseCode}{sheetIndex}.xlsx";
                    using (SpreadsheetDocument document = SpreadsheetDocument.Create(excelFileName, SpreadsheetDocumentType.Workbook))
                    {
                        // this list of attributes will be used when writing a start element
                        List<OpenXmlAttribute> attributes;
                        OpenXmlWriter writer;

                        document.AddWorkbookPart();
                        WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart<WorksheetPart>();

                        writer = OpenXmlWriter.Create(workSheetPart);
                        writer.WriteStartElement(new Worksheet());
                        writer.WriteStartElement(new SheetData());
                        int rowNum = 1;
                        while (reader.Read())
                        {
                            // 如果取消状态为挂起，则取消后续操作
                            if (worker.CancellationPending)
                            {
                                e.Cancel = true;
                                break;
                            }
                            if (rowNum == 1)
                            {
                                CreateSheetHeader(writer, reader);
                                rowNum++;
                            }
                            try
                            {
                                // create a new list of attributes
                                attributes = new List<OpenXmlAttribute>();
                                // add the row index attribute to the list
                                attributes.Add(new OpenXmlAttribute("r", null, rowNum.ToString()));

                                // write the row start element with the row index attribute
                                writer.WriteStartElement(new Row(), attributes);

                                for (int columnNum = 1; columnNum <= reader.FieldCount; ++columnNum)
                                {
                                    // reset the list of attributes
                                    attributes = new List<OpenXmlAttribute>();
                                    // add data type attribute - in this case inline string (you might want to look at the shared strings table)
                                    attributes.Add(new OpenXmlAttribute("t", null, "str"));
                                    // add the cell reference attribute
                                    attributes.Add(new OpenXmlAttribute("r", "", string.Format("{0}{1}", GetColumnName(columnNum), rowNum)));

                                    // write the cell start element with the type and reference attributes
                                    writer.WriteStartElement(new Cell(), attributes);
                                    // write the cell value
                                    writer.WriteElement(new CellValue(ReplaceLowOrderASCIICharacters(reader[columnNum - 1].ToString())));

                                    // write the end cell element
                                    writer.WriteEndElement();
                                }
                                // write the end row element
                                writer.WriteEndElement();
                                // 进度条显示
                                iProcessedCount++;  // 已处理记录数加1
                                int percentComplete =Convert.ToInt32(Math.Round((float)iProcessedCount / argument.RecordCount * 100));
                                worker.ReportProgress(percentComplete);
                                // 根据当前Sheet行数控制循环，当Sheet达到指定容量时重新创建Excel文件
                                rowNum++;           // Sheet行数加1
                                if ((rowNum - 2) / argument.ExcelCapacity == 1)
                                {
                                    rowNum = 1; // Sheet中行标识重置为1
                                    break;
                                }
                            }
                            catch (Exception ex)
                            {
                                LogManager.GetCurrentClassLogger().Error($"Message:{ex.Message} StackTrace:{ex.StackTrace}");
                            }
                        }
                        // write the end SheetData element
                        writer.WriteEndElement();
                        // write the end Worksheet element
                        writer.WriteEndElement();
                        writer.Close();

                        writer = OpenXmlWriter.Create(document.WorkbookPart);
                        writer.WriteStartElement(new Workbook());
                        writer.WriteStartElement(new Sheets());

                        writer.WriteElement(new Sheet()
                        {
                            Name = "LargeDataSheet",
                            SheetId = 1,
                            Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
                        });
                        // End Sheets
                        writer.WriteEndElement();
                        // End Workbook
                        writer.WriteEndElement();
                        writer.Close();
                        document.Close();
                    }
                    // 如果取消状态为挂起，则取消后续操作
                    if (worker.CancellationPending)
                    {
                        e.Cancel = true;
                        break;
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                LogManager.GetCurrentClassLogger().Error($"Message:{ex.Message} StackTrace:{ex.StackTrace}");
            }
        }

        // A simple helper to get the column name from the column index. This is not well tested!
        private static string GetColumnName(int columnIndex)
        {
            int dividend = columnIndex;
            string columnName = String.Empty;
            int modifier;

            while (dividend > 0)
            {
                modifier = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modifier).ToString() + columnName;
                dividend = (int)((dividend - modifier) / 26);
            }

            return columnName;
        }

        private static void CreateSheetHeader(OpenXmlWriter writer, IDataReader reader)
        {
            int rowNum = 1;
            // create a new list of attributes
            List<OpenXmlAttribute> attributes = new List<OpenXmlAttribute>();
            // add the row index attribute to the list
            attributes.Add(new OpenXmlAttribute("r", null, rowNum.ToString()));

            // write the row start element with the row index attribute
            writer.WriteStartElement(new Row(), attributes);

            for (int columnNum = 1; columnNum <= reader.FieldCount; ++columnNum)
            {
                // reset the list of attributes
                attributes = new List<OpenXmlAttribute>();
                // add data type attribute - in this case inline string (you might want to look at the shared strings table)
                attributes.Add(new OpenXmlAttribute("t", null, "str"));
                // add the cell reference attribute
                attributes.Add(new OpenXmlAttribute("r", "", string.Format("{0}{1}", GetColumnName(columnNum), rowNum)));

                // write the cell start element with the type and reference attributes
                writer.WriteStartElement(new Cell(), attributes);
                // write the cell value
                writer.WriteElement(new CellValue(reader.GetName(columnNum - 1)));

                // write the end cell element
                writer.WriteEndElement();
            }
            // write the end row element
            writer.WriteEndElement();
        }

        /// <summary>
        /// 过滤低位非打印字符
        /// </summary>
        /// <param name="tmp"></param>
        /// <returns></returns>
        public static string ReplaceLowOrderASCIICharacters(string tmp)
        {
            StringBuilder info = new StringBuilder();
            foreach (char cc in tmp)
            {
                int ss = (int)cc;
                if (((ss >= 0) && (ss <= 8)) || ((ss >= 11) && (ss <= 12)) || ((ss >= 14) && (ss <= 32)))
                    info.AppendFormat(" ", ss);//&#x{0:X};
                else info.Append(cc);
            }
            return info.ToString();
        }
    }
}
